-- @owner: @zou_jialiang050
-- @date: 2022/02/24
-- @testpoint: 在向src表视图更新数据时，将更新的数值同步至src表中，并在des表中插入计数器数字 after each statement

--step1:建表;expect:成功
drop table if exists t_trigger_src_0042 cascade;
drop table if exists t_trigger_des_tbl_0042 cascade;
drop sequence if exists trigger_des_seq1 cascade;
create table t_trigger_src_0042(id1 int, id2 int, id3 int) with (storage_type=ustore);
create table t_trigger_des_tbl_0042(id1 int, id2 int, id3 int) with (storage_type=ustore);

--step2:在向src表视图更新数据时，将更新的数值同步至src表中，并在des表中插入计数器数字 after each statement;expect:成功
create or replace view t_trigger_src_0042_view1 as select * from t_trigger_src_0042;
create sequence trigger_des_seq1;
create or replace function tri_truncate_func() returns trigger as
$$
declare
begin
insert into t_trigger_des_tbl_0042 values(nextval('trigger_des_seq1'),nextval('trigger_des_seq1'),nextval('trigger_des_seq1'));
return new;
end
$$ language plpgsql;
/
drop trigger if exists truncate_trigger on t_trigger_src_0042_view1;
create trigger truncate_trigger after update on t_trigger_src_0042_view1
for each statement
execute procedure tri_truncate_func();
/
create or replace function insert_func()
returns trigger as $$
begin
update t_trigger_src_0042 set id3=new.id3;
return new;
end;
$$language plpgsql;
/
drop trigger if exists update_trigger_1 on t_trigger_src_0042_view1;
create trigger update_trigger_1
instead of update on t_trigger_src_0042_view1
for each row
execute procedure insert_func();
/
insert into t_trigger_src_0042 values(1,1,1),(2,2,2),(3,3,3);
select * from t_trigger_src_0042 order by id1;
select * from t_trigger_des_tbl_0042 order by id1;
update t_trigger_src_0042_view1 set id3=100;
select * from t_trigger_src_0042 order by id1;
select * from t_trigger_des_tbl_0042 order by id1;

--step3:清理环境;expect:成功
drop table if exists t_trigger_src_0042 cascade;
drop table if exists t_trigger_des_tbl_0042 cascade;
drop sequence if exists trigger_des_seq1 cascade;